/**  
 * 文 件 名: UserMgrDAO.java  
 * 描    述：   
 * 版    权：Copyright (c)20012-2012 tongzhou All Rights Reserved.
 * 公    司: 同舟 
 * 作    者：彭定雄                     
 * 版    本: 1.0  
 * 创建时间: 2012-10-27
 *  
 * 修改历史：  
 * 时间                             作者                       版本                        描述  
 * ------------------------------------------------------------------  
 * 2012-10-27        彭定雄                1.0          1.0 Version  
 */        

/**
 * 
 */
package com.tongzhou.system.userMgr.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.tongzhou.db.DBUtil;
import com.tongzhou.db.exception.DBException;
import com.tongzhou.system.rightsMgmt.model.Right;
import com.tongzhou.system.rightsMgmt.model.Role;
import com.tongzhou.system.userMgr.dao.IUserMgrDAO;
import com.tongzhou.system.userMgr.model.Department;
import com.tongzhou.system.userMgr.model.SearchUserCondition;
import com.tongzhou.system.userMgr.model.User;
import com.tongzhou.util.Util;

public class UserMgrDAO implements IUserMgrDAO{

	@Override
	public int addUser(User user) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("insert into sys_user(username,password,create_by,create_time,active)  ");
		sql.append(" values('"+user.getUsername()+"','"+user.getPassword()+"','"+user.getCreate_by()+"',now(),'"+user.getActive()+"');");
		int result=DBUtil.update(sql.toString());
		return result;
	}

	@Override
	public List<User> listUser(SearchUserCondition condition) throws DBException {
		List<User> result=null;
		
		//查询条件SQL
		//查询参数
		StringBuffer sqlCondition = new StringBuffer() ;
		List<String> paramList = new ArrayList<String>() ;
		
		if(condition.getUsername() != null && !condition.getUsername().equals("")){
			sqlCondition.append(" and s.username like ?") ;
			paramList.add("%"+condition.getUsername()+"%") ;
		}
		if(condition.getDept_name() != null && !condition.getDept_name().equals("")){
			sqlCondition.append(" and d.name like ?") ;
			paramList.add("%"+condition.getDept_name()+"%") ;
		}
		if(condition.getName() != null && !condition.getName().equals("")){
			sqlCondition.append(" and s.name like ?") ;
			paramList.add("%"+condition.getName()+"%") ;
		}
		if(!Util.getAdminUserRoles().contains("1")){
			sqlCondition.append(" and s.id = ?") ;
			paramList.add(String.valueOf(Util.getAdminUserId())) ;
		}
		
		//取得满足条件的总记录数和总页数
		StringBuffer sql = new StringBuffer() ;
		sql.append("select count(*) ");
		sql.append(" from sys_user s left join sys_department d on d.id=s.dept_id ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		
		
		//总记录数
		Map<String,String> dataMap=new HashMap<String,String>();
		long total = DBUtil.getCount(sql.toString(),paramList.toArray()) ;
		//总页数
		long totalPage = Util.getTotalPage(total, condition.getPageSize());
		
		condition.setTotal(total);
		condition.setTotalPage(totalPage);
		
		//查询某页的列表数据
		sql.delete(0, sql.length());
		sql.append("select s.id,s.username,s.password,s.sex,s.email,s.name,s.qq,s.mobile,s.telephone,s.dept_id,s.address,s.active,d.name as dept_name ");
		sql.append(" from sys_user s left join sys_department d on d.id=s.dept_id ");
		sql.append(" where 1=1 ");
		
		sql.append(sqlCondition);
		sql.append(" order by "+condition.getOrderBy()+" "+condition.getOrderByType()) ;
		result = DBUtil.query(sql.toString(), new BeanListHandler<User>(User.class), paramList.toArray(), Util.getStartPos(condition.getCurrentPage(), condition.getPageSize()), condition.getPageSize()) ;
		return result;
	}

	@Override
	public int deleteUser(User user) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("delete from sys_user where id='"+user.getId()+"'");
		return DBUtil.update(sql.toString());
	}

	@Override
	public int updateUser(User user) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("update sys_user s set ");
		sql.append("s.name='"+user.getName()+"',");
		sql.append("s.sex='"+user.getSex()+"',");
		sql.append("s.email='"+user.getEmail()+"',");
		sql.append("s.qq='"+user.getQq()+"',");
		sql.append("s.mobile='"+user.getMobile()+"',");
		sql.append("s.telephone='"+user.getTelephone()+"',");
		sql.append("s.dept_id='"+(Util.checkObjIsNull(user.getDept_id())?"0":user.getDept_id())+"',");
		sql.append("s.address='"+user.getAddress()+"' ");
		sql.append("where s.id='"+user.getId()+"'");
		return DBUtil.update(sql.toString());
	}

	@Override
	public List<User> getUserById(User user) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select s.id,s.username,s.password,s.sex,s.email,s.name,s.qq,s.mobile,s.telephone,s.dept_id,s.address,s.active,d.name as dept_name ");
		sql.append(" from sys_user s left join sys_department d on d.id=s.dept_id ");
		sql.append(" where s.id='"+user.getId()+"' ");
		List<User> result=DBUtil.query(sql.toString(),new BeanListHandler<User>(User.class));
		return result;
	}

	@Override
	public List<User> getUserByName(User user) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select s.id,s.username,s.password,d.name as detp_name ");
		sql.append(" from sys_user s left join sys_department d on d.id=s.dept_id ");
		sql.append(" where s.username='"+user.getUsername()+"' ");
		List<User> result=DBUtil.query(sql.toString(),new BeanListHandler<User>(User.class));
		return result;
	}

	@Override
	public void changePwdByUser(User user) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("update sys_user s set s.password='"+user.getPassword()+"',");
		sql.append("s.update_time=now(),");
		sql.append("s.update_by='"+user.getUpdate_by()+"' ");
		sql.append(" where s.id='"+user.getId()+"'");
		DBUtil.update(sql.toString());
	}
	
	@Override
	public void changePwdByMgmt(User user) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("update sys_user s set s.password='"+user.getPassword()+"',");
		sql.append("s.update_time=now(),");
		sql.append("s.update_by='"+user.getUpdate_by()+"',");
		sql.append("s.active='"+user.getActive()+"' ");
		sql.append(" where s.username='"+user.getUsername()+"'");
		DBUtil.update(sql.toString());
	}
	
	@Override
	public List<User> checkUserByName(User user) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select s.id ");
		sql.append(" from sys_user s ");
		sql.append(" where s.username='"+user.getUsername()+"' ");
		List<User> result=DBUtil.query(sql.toString(),new BeanListHandler<User>(User.class));
		return result;
	}

	@Override
	public List<Department> getAllDepartment() throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select id,name from sys_department ");
		List<Department> result=DBUtil.query(sql.toString(),new BeanListHandler<Department>(Department.class));
		return result;
	}

	@Override
	public List<User> getAllUser() throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select s.id,s.username ");
		sql.append(" from sys_user s ");
		List<User> result=DBUtil.query(sql.toString(),new BeanListHandler<User>(User.class));
		return result;
	}

	@Override
	public List<Role> getUserRoleByUserId(User user) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select u.role_id as id,r.name ");
		sql.append(" from sys_user_role_relation u ");
		sql.append(" inner join sys_role r on r.id=u.role_id ");
		sql.append(" where 1=1 ");
		sql.append(" and u.user_id='"+user.getId()+"'");
		List<Role> result=DBUtil.query(sql.toString(),new BeanListHandler<Role>(Role.class));
		return result;
	}

	@Override
	public void updateUserRole(User user) throws DBException {
		String[] strs=user.getItemsVal().split("`");
		StringBuffer sql=new StringBuffer();
		sql.append("delete from sys_user_role_relation where user_id='"+user.getId()+"'");
		DBUtil.update(sql.toString());
		if(!Util.checkObjIsNull(user.getItemsVal())){
			for(int i=0;i<strs.length;i++){
				sql.delete(0, sql.length());
				sql.append("insert into sys_user_role_relation(role_id,user_id) ");
				sql.append(" values('"+strs[i]+"','"+user.getId()+"'); ");
				DBUtil.update(sql.toString());
			}
		}
	}

	@Override
	public List<Right> getUserUrl(User user) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select distinct sr2.id,sr2.url ");
		sql.append(" from sys_user u inner join sys_user_role_relation su on u.id=su.user_id ");
		sql.append(" inner join sys_role s_r on s_r.id=su.role_id ");
		sql.append(" inner join sys_role_right_relation sr on sr.role_id=su.role_id ");
		sql.append(" inner join sys_right sr2 on sr2.id=sr.right_id ");
//		sql.append(" where sr2.url like '%"+user.getRightUrl()+"%' ");
		sql.append(" where u.id='"+user.getId()+"' and s_r.active='Y' ");
		sql.append(" union ");
		sql.append("select distinct sr2.id,sr2.url ");
		sql.append(" from sys_user u inner join sys_user_role_relation su on u.id=su.user_id ");
		sql.append(" inner join sys_role_groups_relation g on g.role_id=su.role_id ");
		sql.append(" inner join sys_groups s_r on s_r.id=g.groups_id ");
		sql.append(" inner join sys_groups_right_relation sr on sr.groups_id=g.groups_id ");
		sql.append(" inner join sys_right sr2 on sr2.id=sr.right_id ");
//		sql.append(" where sr2.url like '%"+user.getRightUrl()+"%' ");
		sql.append(" where u.id='"+user.getId()+"' and s_r.active='Y' ");
		List<Right> result=DBUtil.query(sql.toString(),new BeanListHandler<Right>(Right.class));
		return result;
	}

	@Override
	public List<Role> getRoleByUserID(User user) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select ru.role_id as id ");
		sql.append(" from sys_user_role_relation ru ");
		sql.append(" where ru.user_id='"+user.getId()+"'");
		List<Role> result=DBUtil.query(sql.toString(),new BeanListHandler<Role>(Role.class));
		return result;
	}
}
